package com.tarena.practice.manage.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import net.sf.json.JSONObject;

public class Excel2JsonUtil {

	/*
	 * 根据excel后缀名调用不同的方法
	 */
	public static List<JSONObject> excContrel2Jsonoller(String fileName){
		//通过excel文件名的后缀决定调用的读取方法并返回Json的List数组
		//fileName:文件名(带后缀)
		List<JSONObject> jsonList = new ArrayList<JSONObject>();
		String filePath = fileName;
		String fileType = filePath.substring(filePath.lastIndexOf(".")+1, filePath.length());
		System.out.println("该excel的后缀为" + fileType);
		switch (fileType) {
		case "xls":
			jsonList = xls2Json(filePath);
			break;
		case "xlsx":
			jsonList = xlsx2Json(filePath);
			break;

		default:
			break;
		}
		return jsonList;
	}
	
	/*
	 * 将获取的excel.xls内容转化为json字符串返回上
	 */
	private static List<JSONObject> xls2Json(String filePath) {
		List<JSONObject> jsonList = new ArrayList<JSONObject>();
		File f = new File(filePath);

		try {
			Workbook book = Workbook.getWorkbook(f);
			for (int sheetNum = 0; sheetNum < book.getNumberOfSheets(); sheetNum++) {
				Sheet sheet = book.getSheet(sheetNum); // 获得所有工作表对象
				if (sheet.getRows() > 0) {
					for (int i = 1; i < sheet.getRows(); i++) {
						JSONObject jsonData = new JSONObject();
						for (int j = 0; j < sheet.getColumns(); j++) {
							Cell cell = sheet.getCell(j, i); // 获得单元格
							jsonData.put(sheet.getCell(j, 0).getContents(), cell.getContents());
						}
						jsonList.add(jsonData);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("读取失败");
		}
		return jsonList;
	}

	/*
	 * 将获取的excel.xlsx内容转化为json字符串返回上
	 */
	private static List<JSONObject> xlsx2Json(String filePath) {
		List<JSONObject> jsonList = new ArrayList<JSONObject>();
		
		try {
			InputStream is = new FileInputStream(filePath);
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
			// 获取每一个工作薄
			for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
				XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
				if (xssfSheet == null) {
					continue;
				}
				// 获取当前工作薄的每一行
				for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
					JSONObject jsonData = new JSONObject();
					XSSFRow xssfRow = xssfSheet.getRow(rowNum);
					XSSFRow row0 = xssfSheet.getRow(0);
					// 如果行数不为空开始读取数据
					if (xssfRow != null) {
						for (int i = 0; i < xssfRow.getLastCellNum(); i++) {
							XSSFCell cell = xssfRow.getCell(i);
							XSSFCell cell0 = row0.getCell(i);
							// 将数据类型设为String
							cell.setCellType(1);
							cell0.setCellType(1);
							jsonData.put(cell0.getStringCellValue(), cell.getStringCellValue());
						}
						jsonList.add(jsonData);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("读取失败");
		}
		return jsonList;
	}
}
